﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using Microsoft.Practices.EnterpriseLibrary.Data;
using System.Data.Common;

namespace DAL.SystemManagement
{
    public  class ServiceRouteTreeDAL
    {

       public static DataTable GetRoutes1(int userID,string userType)
       {
           string sqlstr = String.Format(@"Select a.srt_id, a.srt_rtchnnm  From st_sea_routes a 
                                            Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y' {0} 
                                            union Select a.srt_id, a.srt_rtchnnm From st_sea_routes a 
                                            Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y' 
                                            {0}", userType == "1" ? "": " Inner Join ts_bas_user c On b.sln_cst_agt_id = c.company And c.user_id = " + userID);
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
           return db.ExecuteDataTable(dbCommand);
       }

       public static DataTable GetRoutes1_SL(int userID, string userType)
       {
           string sqlstr = String.Format(@"Select a.srt_id, a.srt_rtchnnm, a.srt_rtcd From st_sea_routes a 
                                            Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y' {0} 
                                            union Select a.srt_id, a.srt_rtchnnm From st_sea_routes a 
                                            Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y' 
                                            {0}", userType == "1" ? "" : " Inner Join ts_bas_user c On b.sln_cst_agt_id = c.company And c.user_id = " + userID);
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
           return db.ExecuteDataTable(dbCommand);
       }

       public static DataTable GetRoutes2(int userID,string userType, int sln_srt_id)
       {
           string userParam = userType == "1" ? "" : " Inner Join ts_bas_user c On b.sln_cst_agt_id = c.company  or b.sln_cst_agt_id is null  And c.user_id = " + userID;
           string sqlstr = String.Format(@"Select b.sln_id,b.sln_name ,b.sln_code ,v.CST_CSTM From st_sea_routes a 
                                           Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y' 
                                            left join st_customers  v on b.sln_cst_vcm_id= v.cst_id 
                                           {0} where b.sln_srt_id={1} ", userParam, sln_srt_id);

           //string sqlstr = "Select b.sln_id,b.sln_name From st_sea_routes a Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y'where b.sln_srt_id=" + sln_srt_id;

           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
           return db.ExecuteDataTable(dbCommand);
       }

       public static DataTable GetRoutes3(int userID, string userType, int sln_srt_id)
       {
           string userParam = userType == "1" ? "" : " And c.user_id = " + userID;

//           string sqlstr = String.Format(@"Select a.sln_id,a.sln_name From st_service_lines a 
//                                            Inner Join ts_bas_user c On a.sln_cst_agt_id = c.company   
//                                            Where a.sln_prt = 'N' and a.sln_prt_id= {0} {1}"
//                                                   , sln_srt_id, userType != "1"? "And c.user_id = " + userID : "");
           string sqlstr = "Select a.sln_id,a.sln_name, a.sln_code ,v.CST_CSTM From st_service_lines a left join st_customers  v on a.sln_cst_vcm_id= v.cst_id   Where a.sln_prt = 'N' and a.sln_prt_id=" + sln_srt_id;

           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
           return db.ExecuteDataTable(dbCommand);
       }

       public static int GetSt_sea_routesBysrt_id(string srt_rtchnnm)
       {
           string sqlstr = String.Format(@"select srt.srt_id from st_sea_routes srt where srt.srt_rtchnnm='{0}'", srt_rtchnnm);
           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
           return int.Parse(db.ExecuteScalar(dbCommand).ToString());
       }

        /// <summary>
        /// 得到船名
        /// </summary>
        /// <param name="userID"></param>
        /// <param name="userType"></param>
        /// <param name="sln_srt_id"></param>
        /// <returns></returns>
       public static DataTable GetVesselName(int sln_id)
       {

           string sqlstr = String.Format(@"select cst_cstmnm from st_customers where 
cst_id =( select sln_cst_vcm_id from st_service_lines t where sln_id={0}) ", sln_id);

           //string sqlstr = "Select b.sln_id,b.sln_name From st_sea_routes a Inner Join st_service_lines b On a.srt_id = b.sln_srt_id And b.sln_prt = 'Y'where b.sln_srt_id=" + sln_srt_id;

           Database db = DatabaseFactory.CreateDatabase();
           DbCommand dbCommand = db.GetSqlStringCommand(sqlstr);
           return db.ExecuteDataTable(dbCommand);
       }

    }//end class
}
